package cn.boltzjf.sql4j.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Objects;
import java.util.Set;
import java.util.function.Consumer;
import java.util.stream.Collectors;

import cn.boltzjf.sql4j.Column;
import cn.boltzjf.sql4j.ResultConverter;
import cn.boltzjf.sql4j.Row;
import cn.boltzjf.sql4j.Sql;
import cn.boltzjf.sql4j.SqlException;
import cn.boltzjf.sql4j.Table;

public abstract class Dao {
    
    protected abstract Connection getConnection();
    
    protected abstract Table table();
    
    private final RowResultConverter resultConverter = new RowResultConverter(valueConverter(), table().allColumns());
    
    protected ValueConverter valueConverter() {
        return ValueConverter.DEFAULT_INSTANCE;
    }
    
    protected ResultConverter<Row> getResultConverter() {
        return resultConverter;
    }
    
    protected Sql sql() {
        return new Sql();
    }
    
    protected Sql sql(String sql) {
        return new Sql(sql);
    }
    
    public int insert(Connection conn, Row row) {
        notNull(conn, "Connection");
        notEmpty(row, "Row");
        List<Column<?>> columns = new ArrayList<>();
        List<Object> values = new ArrayList<>();
        row.forEach((column, value) -> {columns.add(column); values.add(valueConverter().toDb(value, column.type()));});
        return sql("insert into #{table} (#{columns}) values (?{values})")
            .mapName("table", table())
            .mapName("columns", columns)
            .mapParam("values", values)
            .executeUpdate(conn);
    }
    
    public int[] batchInsert(Connection conn, List<Row> rows) {
    	notNull(conn, "Connection");
        notEmpty(rows, "Rows");
        rows.stream().forEach(row -> notEmpty(row, "Any row in the row list"));
        
        Row firstRow = rows.get(0);
        Column<?>[] columns = firstRow.keySet().toArray(new Column<?>[firstRow.size()]);
        notEmpty(columns, "Columns");
        forEach(columns, col -> notNull(col, "Any column in the row"));
        
        rows.forEach(row -> ensure(Objects.equals(firstRow.keySet(), row.keySet()), "Every row should have the same columns"));
        
        Sql sql = sql("insert into #{table} (#{columns}) values (?{rowValues})")
        		.mapName("table", table())
        		.mapName("columns", columns);
        
        List<Map<String, Object>> paramsMapList = new ArrayList<>(rows.size());
        for (Row row : rows) {
        	List<Object> rowValues = new ArrayList<>(columns.length);
        	for (Column<?> column : columns) {
        		rowValues.add(valueConverter().toDb(row.get(column), column.type()));
        	}
        	Map<String, Object> paramsMap = new HashMap<>(1);
        	paramsMap.put("rowValues", rowValues);
        	paramsMapList.add(paramsMap);
        }
        
        return sql.executeBatchUpdate(conn, paramsMapList);
    }
    
    public int update(Connection conn, Row row, Column<?>... keyColumns) {
        notNull(conn, "Connection");
        notEmpty(row, "Row");
        Set<Column<?>> keys = new HashSet<>();
        if (keyColumns != null && keyColumns.length > 0) {
            for (Column<?> keyColumn : keyColumns) {
                if (!row.containsColumn(keyColumn)) {
                    throw new IllegalArgumentException(String.format("Key column %s is not included in row for updating", keyColumn));
                }
                keys.add(keyColumn);
            }
        }
        
        Sql sql = sql("update #{table} set ");
        boolean added = false;
        for (Entry<Column<?>, Object> entry : row.entrySet()) {
            Column<?> col = entry.getKey();
            Object val = entry.getValue();
            if (!keys.contains(col)) {
                sql.append(col).append("=").appendParam(valueConverter().toDb(val, col.type())).append(",");
                added = true;
            }
        }
        if (!added) {
            throw new IllegalArgumentException("No column found to update except for the key columns");
        }
        sql.deleteLastCharsInSql(",".length());
        
        if (keyColumns != null && keyColumns.length > 0) {
            sql.append(" where ");
            for (Column<?> keyColumn : keyColumns) {
                Object keyValue = row.get(keyColumn);
                if (keyValue != null) {
                    sql.append(keyColumn).append("=").appendParam(valueConverter().toDb(keyValue, keyColumn.type()));
                } else {
                    sql.append(keyColumn).append(" is null");
                }
                sql.append(" and ");
            }
            sql.deleteLastCharsInSql(" and ".length());
        }
        sql.mapName("table", table());
        
        return sql.executeUpdate(conn);
    }
    
    public int[] batchUpdate(Connection conn, List<Row> rows, Column<?>... keyColumns) {
    	notNull(conn, "Connection");
        notEmpty(rows, "Rows");
        notEmpty(keyColumns, "Key columns");
        rows.stream().forEach(row -> notEmpty(row, "Any row in the row list"));
        forEach(keyColumns, col -> notNull(col, "Any one of the key columns"));
        
        Set<Column<?>> keyColumnSet = new HashSet<>();
        forEach(keyColumns, col -> keyColumnSet.add(col));
        
        Row firstRow = rows.get(0);
        Set<Column<?>> allColumnSet = new HashSet<>(firstRow.keySet());
        notEmpty(allColumnSet, "Columns");
        allColumnSet.stream().forEach(col -> notNull(col, "Any column in the row"));
        ensure(allColumnSet.containsAll(keyColumnSet), "Every row should contain all key columns");
        rows.forEach(row -> ensure(Objects.equals(allColumnSet, row.keySet()), "Every row should have the same columns"));
        
        Set<Column<?>> updateColumnSet = new HashSet<>(allColumnSet);
        updateColumnSet.removeAll(keyColumnSet);
        notEmpty(updateColumnSet, "Columns to be updated");
        
        Column<?>[] updateColumns = updateColumnSet.toArray(new Column<?>[updateColumnSet.size()]);
        
        Set<Column<?>> nullKeyColumnSet = keyColumnSet.stream().filter(col -> firstRow.get(col) == null).collect(Collectors.toSet());
        rows.stream().forEach(row -> nullKeyColumnSet.stream().forEach(col -> ensure(row.get(col) == null, "Not key column '%s' of all rows are null", col)));
        
        Sql sql = sql("update #{table} set ");
        for (Column<?> col : updateColumns) {
        	sql.append(col.name()).append("=?{").append(col.name()).append("},");
        }
        sql.deleteLastCharsInSql(",".length());
        sql.append(" where ");
        for (Column<?> keyColumn : keyColumns) {
            if (!nullKeyColumnSet.contains(keyColumn)) {
            	sql.append(keyColumn.name()).append("=?{").append(keyColumn.name()).append("}");
            } else {
            	sql.append(keyColumn.name()).append(" is null");
            }
            sql.append(" and ");
        }
        sql.deleteLastCharsInSql(" and ".length());
        sql.mapName("table", table());
        
        List<Map<String, Object>> paramsMapList = new ArrayList<>(rows.size());
        for (Row row : rows) {
        	Map<String, Object> paramsMap = new HashMap<>();
        	for (Column<?> updateColumn : updateColumns) {
        		paramsMap.put(updateColumn.name(), valueConverter().toDb(row.get(updateColumn), updateColumn.type()));
        	}
        	for (Column<?> keyColumn : keyColumns) {
        		if (!nullKeyColumnSet.contains(keyColumn)) {
        			paramsMap.put(keyColumn.name(), valueConverter().toDb(row.get(keyColumn), keyColumn.type()));
        		}
        	}
        	paramsMapList.add(paramsMap);
        }
        
        return sql.executeBatchUpdate(conn, paramsMapList);
    }
    
    public int delete(Connection conn, Row by) {
        notNull(conn, "Connection");
        Sql sql = new Sql("delete from #{table}");
        if (by != null && !by.isEmpty()) {
            sql.append(" where ");
            for (Entry<Column<?>, Object> entry : by.entrySet()) {
                Column<?> col = entry.getKey();
                Object val = entry.getValue();
                sql.append(col);
                if (val != null) {
                    sql.append("=").appendParam(valueConverter().toDb(val, col.type()));
                } else {
                    sql.append(" is null");
                }
                sql.append(" and ");
            }
            sql.deleteLastCharsInSql(" and ".length());
        }
        sql.mapName("table", table());
        return sql.executeUpdate(conn);
    }
    
    public List<Row> getList(Connection conn, Row by, Column<?>... columns) {
        notNull(conn, "Connection");
        notEmpty(columns, "Columns");
        Sql sql = new Sql("select #{columns} from #{table}");
        if (by != null && !by.isEmpty()) {
            sql.append(" where ");
            for (Entry<Column<?>, Object> entry : by.entrySet()) {
                Column<?> col = entry.getKey();
                Object val = entry.getValue();
                sql.append(col);
                if (val != null) {
                    sql.append("=").appendParam(valueConverter().toDb(val, col.type()));
                } else {
                    sql.append(" is null");
                }
                sql.append(" and ");
            }
            sql.deleteLastCharsInSql(" and ".length());
        }
        sql.mapName("columns", columns).mapName("table", table());
        return sql.executeQuery(conn, getResultConverter());
    }
    
    public Row getOne(Connection conn, Row by, Column<?>... columns) {
        List<Row> rows = getList(conn, by, columns);
        if (rows != null && !rows.isEmpty()) {
            return rows.get(0);
        }
        return null;
    }
    
    public int insert(Row row) {
        try (Connection conn = getConnection()) {
            return insert(conn, row);
        } catch (SQLException e) {
            throw new SqlException("Failed to close connection: " + e.getMessage(), e);
        }
    }
    
    public int update(Row row, Column<?>... keyColumns) {
        try (Connection conn = getConnection()) {
            return update(conn, row, keyColumns);
        } catch (SQLException e) {
            throw new SqlException("Failed to close connection: " + e.getMessage(), e);
        }
    }
    
    public int delete(Row by) {
        try (Connection conn = getConnection()) {
            return delete(conn, by);
        } catch (SQLException e) {
            throw new SqlException("Failed to close connection: " + e.getMessage(), e);
        }
    }
    
    public List<Row> getList(Row by, Column<?>... columns) {
        try (Connection conn = getConnection()) {
            return getList(conn, by, columns);
        } catch (SQLException e) {
            throw new SqlException("Failed to close connection: " + e.getMessage(), e);
        }
    }
    
    public Row getOne(Row by, Column<?>... columns) {
        try (Connection conn = getConnection()) {
            return getOne(conn, by, columns);
        } catch (SQLException e) {
            throw new SqlException("Failed to close connection: " + e.getMessage(), e);
        }
    }
    
    
    
    private void ensure(boolean expected, String errorMessage, Object... errorParams) {
    	if (!expected) {
    		throw new IllegalArgumentException(errorParams != null && errorParams.length > 0 ? String.format(errorMessage, errorParams) : errorMessage);
    	}
    }
    
    private static void notNull(Object param, String paramName) {
        if (param == null) {
            throw new IllegalArgumentException(String.format("%s should not be null", paramName));
        }
    }
    
    private static void notEmpty(Collection<?> param, String paramName) {
        if (param == null || param.isEmpty()) {
            throw new IllegalArgumentException(String.format("%s should not be null or empty", paramName));
        }
    }
    
    private static void notEmpty(Map<?,?> param, String paramName) {
        if (param == null || param.isEmpty()) {
            throw new IllegalArgumentException(String.format("%s should not be null or empty", paramName));
        }
    }
    
    private static void notEmpty(Object[] param, String paramName) {
        if (param == null || param.length == 0) {
            throw new IllegalArgumentException(String.format("%s should not be null or empty", paramName));
        }
    }
    
    private static <T> void forEach(T[] param, Consumer<T> consumer) {
    	for (T t : param) {
    		consumer.accept(t);;
    	}
    }

}
